import random
import redis
import xlrd
import xlwt
import calendar
import datetime
import pandas as pd
import chinese_calendar


class DefectGeneration():
    #redis 连接配置
    global gl_host
    global gl_port
    global gl_db
    gl_host='127.0.0.1'
    gl_port=6379
    gl_db=1
    #配置日期生成月份
    global month_redis
    global year_redis
    year_redis="2021"
    month_redis=["01","02","03","04","05","06","07","08","09","10","11","12"]
    #设置负责人
    global usernameGlobal
    usernameGlobal="周仕彦"
    #设置初始文档
    global path
    path="D:/工作产出/测试文档/2023-02-10 缺陷编写/2019-2022测试用例全量.xlsx"

    #设置读取的sheet
    global sheet_name_read
    sheet_name_read="2021"
    #设置生成文档地址
    global newPath
    newPath="D:/工作产出/测试文档/2023-02-10 缺陷编写/2021-测试用例.xls"

    global userChanDao
    userChanDao={"政采贷":["dengligang","chenlang","shenziji"],"烟商贷":["wangzongyue"]}
    def readUseCases(self):
        print("开始执行BUG生成")
        global path
        global sheet_name_read
        self.createRedisTime()
        workbook = xlrd.open_workbook(path)
        sheet1_is_load = workbook.sheet_by_name(sheet_name_read)
        nrows=sheet1_is_load.nrows
        style = xlwt.XFStyle()
        style.alignment.wrap = 1
        workbookNew = xlwt.Workbook(encoding=ascii)
        sheet_write = workbookNew.add_sheet('测试用例')
        sheet_bug=workbookNew.add_sheet('测试bug')
        list=[]
        global usernameGlobal
        for i in range(nrows):
                projectName = sheet1_is_load.cell_value(i,3)
                year=sheet1_is_load.cell_value(i,0)
                month=sheet1_is_load.cell_value(i,1)
                username=sheet1_is_load.cell_value(i,12)
                if(username==usernameGlobal):
                    createTime=self.timeChick(month,year)
                    case_title = sheet1_is_load.cell_value(i, 7)
                    case_step = sheet1_is_load.cell_value(i, 8)
                    hope_result = sheet1_is_load.cell_value(i, 9)
                    result=sheet1_is_load.cell_value(i, 10)
                    dict ={}
                    dict['projectName']=projectName
                    dict['createTime']=createTime
                    dict['case_title']=case_title
                    dict['hope_step']=''
                    dict['case_step']=case_step
                    dict['hope_result']=hope_result
                    dict['result']=result
                    # 将结果写入
                    list.append(dict)
        #第一个sheet
        self.createTitle(sheet_write)
        #第二个sheet
        self.createTitleBug(sheet_bug)
        global userChanDao
        zhinengfengkong="hechen"
        Epiaobao="dengligang"
        huochebangyunfeidai=["zhuxin","chenlang"]
        caiyunEdai="pengyangyang"
        guangwangguanwei=["jiangyuanlin","chengsai","yangchengxiu"]
        huochebanghuaxia="zhuxin"
        severity=["2","3","4"]
        first=["1","2","3","4"]
        for i in range(len(list)):
            tall_style = xlwt.easyxf('font:height 720')
            first_row = sheet_write.row(i+1)
            first_row.set_style(tall_style)
            sheet_write.write(i+1, 0, list[i]['projectName'])
            sheet_write.write(i+1, 1, list[i]['createTime'])
            sheet_write.write(i+1, 2, list[i]['case_title'])
            sheet_write.write(i+1, 3, list[i]['hope_step'])
            sheet_write.write(i+1, 4, list[i]['case_step'])
            sheet_write.write(i+1, 5, list[i]['hope_result'])
            sheet_write.write(i+1, 6, list[i]['result'])

            #bug界面
            tall_style = xlwt.easyxf('font:height 720')
            first_row = sheet_bug.row(i+1)
            first_row.set_style(tall_style)
            sheet_bug.write(i + 1, 1, list[i]['createTime'])
            if (list[i]['projectName'].find("智能风控系统")) != -1:
                sheet_bug.write(i + 1, 0, "智能风控系统")
                sheet_bug.write(i + 1, 2, zhinengfengkong)
            elif (list[i]['projectName'].find("E票宝")) != -1:
                sheet_bug.write(i + 1, 0, "E票宝")
                sheet_bug.write(i + 1, 2, Epiaobao)
            elif (list[i]['projectName'].find("货车帮运费贷")) != -1:
                sheet_bug.write(i + 1, 0, "货车帮运费贷")
                sheet_bug.write(i + 1, 2, random.choice(huochebangyunfeidai))
            elif (list[i]['projectName'].find("采云E贷")) != -1:
                sheet_bug.write(i + 1, 0, "采云E贷")
                sheet_bug.write(i + 1, 2, caiyunEdai)
            elif (list[i]['projectName'].find("官网官微")) != -1:
                sheet_bug.write(i + 1, 0, "官网官微")
                sheet_bug.write(i + 1, 2, random.choice(guangwangguanwei))
            elif (list[i]['projectName'].find("货车帮（华夏）")) != -1:
                sheet_bug.write(i + 1, 0, "货车帮（华夏）")
                sheet_bug.write(i + 1, 2, huochebanghuaxia)
            else:
                sheet_bug.write(i + 1, 0, list[i]['projectName'])
            title = list[i]['case_title'].replace("验证", "").replace("是否正常", "").replace("是否","").replace("是否正确","").replace("正确","")
            newTitle=self.titleHandle(title)
            sheet_bug.write(i + 1, 3, newTitle)
            sheet_bug.write(i + 1, 4, list[i]['case_step'])
            sheet_bug.write(i + 1, 5, newTitle)
            sheet_bug.write(i + 1, 6, list[i]['case_title'].replace("验证", "").replace("是否正常", "") + "正常")
            sheet_bug.write(i + 1, 7, random.choice(severity))
            sheet_bug.write(i + 1, 8, random.choice(first))
        global newPath
        workbookNew.save(newPath)
        print("执行完毕")

    #对缺陷标题进行处理
    def titleHandle(self,title):
        listMessage=["异常","错误","与需求不符合","失败","报错","参数错误"]
        if("上传" in title):
            return title.replace("上传","上传失败")
        elif("提示" in title):
            return title.replace("提示","提示错误")
        elif("输入" in title):
            return title.replace("输入", "输入校验与需求不符")
        elif("获取" in title):
            return title.replace("获取", "获取相关后端接口响应500")
        else:
            return title + random.choice(listMessage)

    #获取redis连接
    def redisConnect(self):
        global gl_host
        global gl_port
        global gl_db
        pool = redis.ConnectionPool(host=gl_host,port=gl_port,db=gl_db)  # 实现一个连接池
        r = redis.Redis(connection_pool=pool)
        return r

    def timeChick(self,month,year):
        r=self.redisConnect()
        test = year + "-" + month
        if(month=="10"):
            newMonth = int(month)
            newYear = int(year)
        else:
            newMonth = int(month.replace('0', ''))
            newYear = int(year)
        day = calendar.monthrange(newYear, newMonth)[1]
        #获取工作日
        start_time = datetime.datetime(newYear, newMonth, 1)
        end_time = datetime.datetime(newYear, newMonth, day)
        # 获取工作日
        workdays = pd.DataFrame(chinese_calendar.get_workdays(start_time, end_time))
        workdays = workdays.rename(columns={0: '日期'})
        endDay = datetime.datetime(newYear, newMonth, day)
        endTime = endDay.strftime("%Y-%m-%d")
        list=[]
        lenWorkdays=len(workdays['日期'])
        for i in range(lenWorkdays):
            var=workdays['日期'][i].strftime("%Y-%m-%d")
            list.append(var)
        for i in list:
            numberRedis=int(r.hget(test, i))
            newNuber=numberRedis+1
            if(numberRedis<15):
                r.hset(test,i,str(newNuber))
                return i
            elif(i==workdays["日期"][lenWorkdays-1]):
                r.hset(test,i,str(newNuber))
                return i
            else:
                continue

    def createRedisTime(self):
        global month_redis
        global year_redis
        r=self.redisConnect()
        for j in month_redis:
            test = year_redis + "-" + j
            if (j == "10"):
                newMonth = int(j)
                newYear = int(year_redis)
            else:
                newMonth = int(j.replace('0', ''))
                newYear = int(year_redis)
            # newMonth = int(j.replace('0', ''))
            # newYear=int(year_redis)
            day = calendar.monthrange(newYear, newMonth)[1]
            start_time = datetime.datetime(newYear, newMonth, 1)
            end_time = datetime.datetime(newYear, newMonth, day)
            # 获取工作日
            workdays=pd.DataFrame(chinese_calendar.get_workdays(start_time, end_time))
            workdays = workdays.rename(columns={0: '日期'})
            for i in range(len(workdays['日期'])):
                r.hset(test,workdays['日期'][i].strftime("%Y-%m-%d"),"0")


    def createTitle(self,worksheet):
        worksheet.set_panes_frozen('1')
        worksheet.set_horz_split_pos(1)
        for i in range(7):
            first_col = worksheet.col(i)
            first_col.width = 256 * 20
        worksheet.write(0,0,"所属产品(项目名称)")
        worksheet.write(0, 1, "用例创建时间")
        worksheet.write(0, 2, "用例标题")
        worksheet.write(0, 3, "前置条件")
        worksheet.write(0, 4, "用例步骤")
        worksheet.write(0, 5, "预期")
        worksheet.write(0, 6, "结果(通过/失败)")
        return worksheet


    def createTitleBug(self,worksheet):
        worksheet.set_panes_frozen('1')
        worksheet.set_horz_split_pos(1)
        for i in range(9):
            first_col = worksheet.col(i)
            first_col.width = 256 * 20
        worksheet.write(0,0,"所属产品")
        worksheet.write(0, 1, "测试bug创建时间")
        worksheet.write(0, 2, "bug指派处理人(禅道账户)")
        worksheet.write(0, 3, "bug标题")
        worksheet.write(0, 4, "步骤")
        worksheet.write(0, 5, "结果")
        worksheet.write(0, 6, "期望")
        worksheet.write(0, 7, "严重程度(1234)")
        worksheet.write(0, 8, "优先级(1234)")
        return worksheet

if __name__ == '__main__':
     excult=DefectGeneration()
     excult.readUseCases()
